/**
 *
 */
package ipebg.mzk.provider.system.dao;

import ipebg.mzk.common.core.dao.impl.DBDao;
import ipebg.mzk.common.util.HqlUtil;
import ipebg.mzk.common.util.Page;
import ipebg.mzk.facade.entity.MsAdminUser;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;



@Repository("queryForPagesDAO")
public class QueryForPagesDAO<T> extends DBDao<T> {

	protected Class<T> clazz;
	
	public QueryForPagesDAO() {
	}

	public QueryForPagesDAO(Class<T> clazz) {
		super(clazz);
		this.clazz=clazz;
	}

	/**
	 * SQL Query page data
	 *
	 * @param pageNo
	 * @param pageSize
	 * @param hql
	 * @param map
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page findPageBySqlQuery(int pageNo, int pageSize, String sql) {
		return findPageBySql(null, pageNo, pageSize, sql);
	}


	public <E> Page findPageBySql(Class<E> cls,int pageNo, int pageSize, String sql){
		Page page = null;
		List<E> result = null;
		try {
			Query s = getSession().createSQLQuery(sql);
			long rowCount = getTotalBySql(sql);
			setUpPageQuery(s,pageNo,pageSize,cls);
			result = s.list();
			page = new Page(pageNo, pageSize, rowCount, result);
		} catch (RuntimeException re) {
			throw re;
		}
		return page;
	}


	@SuppressWarnings("unchecked")
	public Page findPageBySqlQuery01(int pageNo, int pageSize, String sql) {
		Page page = null;
		List<T> result = null;
		try {
			org.hibernate.SQLQuery s = getSession().createSQLQuery(sql); // .c
			org.hibernate.SQLQuery s2 = getSession().createSQLQuery(sql); // .c
			result = s.addEntity(MsAdminUser.class).list();
			/*result = query.addEntity(MsAdminUser.class).list();*/
			long rowCount = result.size();
			//System.out.println("findPageBySqlQuery row size:"+rowCount);
			result = null;
			System.out.println(result==null);

			s2.setFirstResult((pageNo - 1) * pageSize);
			s2.setMaxResults(pageSize);
			result = s2.addEntity(MsAdminUser.class).list();
			//System.out.println("findPageBySqlQuery result size:"+result.size());
			page = new Page(pageNo, pageSize, rowCount, result);
		} catch (RuntimeException re) {
			throw re;
		}
		return page;
	}




	@SuppressWarnings("unchecked")
	public Page findPageByhqlQuery(int pageNo, int pageSize, String hql) {
		return findPageByHql(null, pageNo, pageSize, hql);
	}

	
	public <E> Page findPageByHql(Class<E> cls,int pageNo, int pageSize, String hql) {
		Page page = null;
		List<E> result = null;
		try {
			Session session = getSession();
			org.hibernate.Query s = session.createQuery(hql); 
			s.setCacheable(true);  
			long rowCount = getTotalByHql(hql);
			setUpPageQuery(s,pageNo,pageSize,cls);
			result = s.list();
			page = new Page(pageNo, pageSize, rowCount, result);
		} catch (RuntimeException re) {
			re.printStackTrace();
			throw re;
		}
		return page;
	}

	public <E> void setUpPageQuery(Query query,int pageNo,int pageSize,Class<E> cls) {
		query.setFirstResult((pageNo - 1) * pageSize);
		query.setMaxResults(pageSize);
		if (cls != null) {
			if (cls == Map.class) {
				query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			}else{
				query.setResultTransformer(Transformers.aliasToBean(cls));
			}
		}
	}

	public long getTotalBySql(String sql) {
		Session session = getSession();
		String countSql = "select count(*) from (" + sql + ") ct";
		Query countQ = getSession().createSQLQuery(countSql);
		return ((BigDecimal) countQ.uniqueResult()).longValue();
	}

	public long getTotalByHql(String hql) {
		Session session = getSession();
		String countSql = "select count(*) from (" + HqlUtil.hql2Sql(session, hql) + ") ct";
		Query countQ = getSession().createSQLQuery(countSql);
		return Long.valueOf(countQ.uniqueResult().toString());
	}

	 /**
	 * @function 分页显示符合所有的记录数，将查询结果封装为Pager
	 * @param pageNo
	 *            当前页数
	 * @param pageSize
	 *            每页显示的条数
	 * @param instance
	 *            将查询条件封装为专家Bean
	 * @return 查询结果Pager
	 */
	@SuppressWarnings("unchecked")
	public List<T> findPageByQuery(int pageNo, int pageSize, String hql,
			Map<Serializable, Serializable> map) {
		List<T> result = null;
		try {
			Query query = createQuery(hql);

			Iterator<Serializable> it = map.keySet().iterator();
			while (it.hasNext()) {
				Object key = it.next();
				query.setParameter(key.toString(), map.get(key));
			}

			query.setFirstResult((pageNo - 1) * pageSize);
			query.setMaxResults(pageSize);

			result = query.list();

		} catch (RuntimeException re) {
			throw re;
		}
		return result;
	}

	/**
	 * @function 根据查询条件查询记录数的个数
	 * @param hql
	 *            hql查询语句
	 * @param map
	 *            用map封装查询条件
	 *            相應的封裝條件以字符串數組String[]的形式封裝進MAP中
	 * @return 数据库中满足查询条件的数据的条数
	 */
	public int getTotalCount(String hql, Map<Serializable, Serializable> map) {
		try {
			Query query = createQuery(hql);

			Iterator<Serializable> it = map.keySet().iterator();
			while (it.hasNext()) {
				Object key = it.next();
				query.setParameter(key.toString(), map.get(key));
			}

			Integer i = (Integer) query.list().get(0);
			return i;
		} catch (RuntimeException re) {
			throw re;
		}

	}



	public Page findPageByCriteria(int pageNo, int pageSize, Map map, T t,String userId) {
		Page page = null;
		try {
			Criteria criteria = this.getSession().createCriteria(t.getClass());

			if (map != null) {
				Set<String> keys = map.keySet();
				for (String key : keys) {
					criteria.add(Restrictions.like(key, map.get(key)));
				}
			}
			criteria.add(Restrictions.ne("teacherNo", userId));

			// 获取根据条件分页查询的总行数
			Long rowCount = ((Number) criteria.setProjection(
					Projections.rowCount()).uniqueResult()).longValue();
			//

			criteria.setProjection(null);

			criteria.setFirstResult((pageNo - 1) * pageSize);
			criteria.setMaxResults(pageSize);

			List<T> result = criteria.list();

			page = new Page(pageNo, pageSize, rowCount, result);

		} catch (RuntimeException re) {
			re.printStackTrace();
			throw re;
		}
		return page;

	}

	/**
	 * @function 分页显示符合所有的记录数，将查询结果封装为Pager
	 * @param pageNo
	 *            当前页数
	 * @param pageSize
	 *            每页显示的条数
	 * @param map
	 *            将查询条件封装为map
	 *            相應的封裝條件以字符串數組String[]的形式封裝進MAP中
	 * @return 查询结果Pager
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Page findPageByCriteria(final int pageNo, final int pageSize,
			final Map map, final T t, final String orderStr, final String flag) {
		Criteria criteria = getSession().createCriteria(t.getClass());
		if (map != null && map.size() > 0) {
			Set<String> keys = map.keySet();
			for (String key : keys) {
				criteria.add(Restrictions.like(key, map.get(key)));
			}
		}
		// 获取根据条件分页查询的总行数
		Long rowCount = ((Number)( criteria.setProjection(
				Projections.rowCount()).uniqueResult())).longValue();
		//
		criteria.setProjection(null);

		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);

		if (flag.equalsIgnoreCase("asc")) {
			criteria.addOrder(Order.asc(orderStr));
		} else {
			criteria.addOrder(Order.desc(orderStr));
		}

		List<T> result = criteria.list();

		return new Page(pageNo, pageSize, rowCount, result);
	}

	/**
	 * @function 分页显示符合所有的记录数，将查询结果封装为Pager
	 * @param pageNo
	 *            当前页数
	 * @param pageSize
	 *            每页显示的条数
	 * @param map
	 *            将查询条件封装为map
	 *            相應的封裝條件以字符串數組String[]的形式封裝進MAP中
	 * @return 查询结果Pager
	 *
	 * add by tony f3221462 for multi-column sorting.
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Page findPageBySqlRestrictionCriteria(final int pageNo, final int pageSize,
			final Map map, final Class cls, final String sql) {
		Criteria criteria = getSession().createCriteria(cls);
		if (map != null && map.size() > 0) {
			Set<String> keys = map.keySet();
			for (String key : keys) {
				criteria.add(Restrictions.like(key, map.get(key)));
			}
		}
		criteria.add(Restrictions.sqlRestriction(sql));

		Long rowCount = ((Number) criteria.setProjection(
				Projections.rowCount()).uniqueResult()).longValue();
		criteria.setProjection(null);
		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);
		return new Page(pageNo, pageSize, rowCount, criteria.list());
	}

	/**
	 * @function 分页显示符合所有的记录数，将查询结果封装为Pager
	 * @param pageNo
	 *            当前页数
	 * @param pageSize
	 *            每页显示的条数
	 * @param map
	 *            将查询条件封装为map
	 *            相應的封裝條件以字符串數組String[]的形式封裝進MAP中
	 * @return 查询结果Pager
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Page findPageByCriteria(final int pageNo, final int pageSize,
			final Map map, final T t, final String[] orderStrs,
			final String flag) {
		Criteria criteria = getSession().createCriteria(t.getClass());
		if (map != null && map.size() > 0) {
			Set<String> keys = map.keySet();
			for (String key : keys) {
				criteria.add(Restrictions.like(key, map.get(key)));
			}
		}

		// 获取根据条件分页查询的总行数
		Long rowCount = ((Number) criteria.setProjection(
				Projections.rowCount()).uniqueResult()).longValue();
		//

		criteria.setProjection(null);

		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);

		if (flag.equalsIgnoreCase("asc")) {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.asc(orderStrs[i]));
			}
		} else {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.desc(orderStrs[i]));
			}
		}
		List<T> result = criteria.list();

		return new Page(pageNo, pageSize, rowCount, result);
	}

	/**
	 * 对数据库中的字段进行is null或者is not null查询
	 *
	 * @param pageNo
	 * @param pageSize
	 * @param map
	 * @param t
	 * @param orderStrs
	 * @param flag
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Page findPageByCriteriaWithNull(final int pageNo,
			final int pageSize, final Map map, final T t,
			final String[] orderStrs, final String flag) {
		Criteria criteria = getSession().createCriteria(t.getClass());
		if (map != null && map.size() > 0) {
			Set<String> keys = map.keySet();
			for (String key : keys) {

				if (map.get(key).equals("isNull")) {
					criteria.add(Restrictions.isNull(key));
				} else if (map.get(key).equals("isNotNull")) {
					criteria.add(Restrictions.isNotNull(key));
				} else {
					criteria.add(Restrictions.like(key, map.get(key)));
				}
			}
		}

		// 获取根据条件分页查询的总行数
		Long rowCount = ((Number) criteria.setProjection(
				Projections.rowCount()).uniqueResult()).longValue();
		//

		criteria.setProjection(null);

		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);

		if (flag.equalsIgnoreCase("asc")) {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.asc(orderStrs[i]));
			}
		} else {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.desc(orderStrs[i]));
			}
		}
		List<T> result = criteria.list();

		return new Page(pageNo, pageSize, rowCount, result);

	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Page findPageByCriteriaAndIn(final int pageNo,
			final int pageSize, final Map map, final T t,
			final String[] orderStrs, final String flag) {
		Criteria criteria = getSession().createCriteria(t.getClass());

		if (map != null && map.size() > 0) {
			Set<String> keys = map.keySet();
			for (String key : keys) {
				if(map.get(key) instanceof Object[]){
					criteria.add(Restrictions.in(key, (Object[]) map.get(key)));
				}else{
					criteria.add(Restrictions.eq(key, map.get(key)));
				}

			}
		}

		// 获取根据条件分页查询的总行数
		Long rowCount = ((Number) criteria.setProjection(
				Projections.rowCount()).uniqueResult()).longValue();
		//

		criteria.setProjection(null);

		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);

		if (flag.equalsIgnoreCase("asc")) {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.asc(orderStrs[i]));
			}
		} else {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.desc(orderStrs[i]));
			}
		}
		List<T> result = criteria.list();

		return new Page(pageNo, pageSize, rowCount, result);
	}


	/**
	 * 處理分頁中用到in 以及  like 的情況
	 * 使用方法：    like：在map中放入String數組,數組的第一個值設為"like",第二個值為like查詢的條件
	 * @param pageNo
	 * @param pageSize
	 * @param map
	 * @param t
	 * @param orderStrs
	 * @param flag
	 * @return Page
	 * @Author:F3225959
	 * @Date:2011/04/11
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Page findPageByCriteriaAndInLike(final int pageNo,
			final int pageSize, final Map map, final T t,
			final String[] orderStrs, final String flag) {
		Criteria criteria = getSession().createCriteria(t.getClass());

		if (map != null && map.size() > 0) {
			Set<String> keys = map.keySet();
			for (String key : keys) {
				if(map.get(key) instanceof Object[]){
					//對like進行判斷
					if(map.get(key) instanceof String[]) {
						String[] f = (String[]) map.get(key);
						if(f[0].equals("like")) {
							criteria.add(Restrictions.like(key, f[1]));
						} else {
							criteria.add(Restrictions.in(key, (Object[]) map.get(key)));
						}
					}
				}else{
					criteria.add(Restrictions.eq(key, map.get(key)));
				}

			}
		}

		// 获取根据条件分页查询的总行数
		Long rowCount = ((Number) criteria.setProjection(
				Projections.rowCount()).uniqueResult()).longValue();
		//

		criteria.setProjection(null);

		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);

		if (flag.equalsIgnoreCase("asc")) {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.asc(orderStrs[i]));
			}
		} else {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.desc(orderStrs[i]));
			}
		}
		List<T> result = criteria.list();

		return new Page(pageNo, pageSize, rowCount, result);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Page findPageByCriteriaAndIn1(final int pageNo,
			final int pageSize, final Map map, final String[] p, final T t,
			final String[] orderStrs, final String flag) {
		Criteria criteria = getSession().createCriteria(t.getClass());


		if (map != null && map.size() > 0) {
			Set<String> keys = map.keySet();
			for (String key : keys) {
				if(map.get(key) instanceof Object[]){
					criteria.add(Restrictions.in(key, (Object[]) map.get(key)));
				}else{
					criteria.add(Restrictions.eq(key, map.get(key)));
				}

			}
		}

				/*
				for(String temp:p) {
					System.out.println("EvaluatetaskId= " + temp);
				}*/
		criteria.add(Restrictions.in("evaluatetaskId", p));

		// 获取根据条件分页查询的总行数
		Long rowCount = ((Number) criteria.setProjection(
				Projections.rowCount()).uniqueResult()).longValue();
		//

		criteria.setProjection(null);

		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);

		if (flag.equalsIgnoreCase("asc")) {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.asc(orderStrs[i]));
			}
		} else {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.desc(orderStrs[i]));
			}
		}
		List<T> result = criteria.list();

		return new Page(pageNo, pageSize, rowCount, result);
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Page findPageByCriteriaOneMonth(final int pageNo,
			final int pageSize, final Map map, final T t,
			final String[] orderStrs, final String flag) {
		Criteria criteria = getSession().createCriteria(t.getClass());

		if (map != null && map.size() > 0) {
			Set<String> keys = map.keySet();
			for (String key : keys) {
				if(map.get(key).equals("updateDate")){
					Calendar c = Calendar.getInstance();
					Calendar c2 = (Calendar) c.clone();
					c.add(Calendar.MONTH, -1);
					criteria.add(Restrictions.between(key, c.getTime(), c2.getTime()));
				}else{
					criteria.add(Restrictions.eq(key, map.get(key)));
				}

			}
		}


		// 获取根据条件分页查询的总行数
		Long rowCount = ((Number) criteria.setProjection(
				Projections.rowCount()).uniqueResult()).longValue();
		//

		criteria.setProjection(null);

		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);

		if (flag.equalsIgnoreCase("asc")) {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.asc(orderStrs[i]));
			}
		} else {
			for (int i = 0; i < orderStrs.length; i++) {
				criteria.addOrder(Order.desc(orderStrs[i]));
			}
		}
		List<T> result = criteria.list();

		return new Page(pageNo, pageSize, rowCount, result);

	}

}
